package com.google.code.jholidays.io.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.google.code.jholidays.core.DescriptorCollection;
import com.google.code.jholidays.core.EventDescriptor;
import com.google.code.jholidays.io.IDescriptorReader;

/**
 * JDBC based implementation of {@link IDescriptorReader} interface. Valid
 * {@link Connection} object must be passed to constructor of {@link JdbcReader}
 * in order to perform reading of event descriptors from database
 * <p>
 * All event descriptors should be stored inside one separate table. Here is
 * CREATE TABLE script for <b>PostgreSQL</b>:
 * <p>
 * <code>
 * CREATE TABLE event_descriptors<br>
 * (<br>
 * id serial NOT NULL,<br>
 * "name" character varying NOT NULL,<br>
 * description character varying NOT NULL,<br>
 * parent_id integer,<br>
 * expression character varying NOT NULL<br>
 * )<br>
 * </code>
 * <p>
 * And here is CREATE TABLE script for <b>SQLite</b>:
 * <p>
 * <code>
 * CREATE TABLE "event_descriptors" (<br>
 *   "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,<br>
 *   "name" TEXT NOT NULL,<br>
 *   "description" TEXT NOT NULL,<br>
 *   "parent_id" INTEGER,<br>
 *   "expression" TEXT NOT NULL<br>
 * )<br>
 * </code>
 * <p>
 * Each column in this table corresponds to event descriptor's field.
 * <tt>parent_id</tt> column may contain nulls, other columns can't.
 * 
 * @author tillias
 * @see IDescriptorReader
 * @see EventDescriptor
 * 
 */
public class JdbcReader implements IDescriptorReader {

    /**
     * Creates new instance of JDBC reader
     * 
     * @param conn
     *            Valid, opened connection which will be used to read event
     *            descriptors
     * @param tableName
     *            Database table name where descriptors are stored
     */
    public JdbcReader(Connection conn, String tableName) {
	this.conn = conn;
	this.tableName = tableName;
    }

    /**
     * {@inheritDoc}
     * <p>
     * This implementation attempts to read event descriptors from given
     * database. Database {@link Connection} object and table name are passed to
     * constructor of this object. If any error occurred during reading (e.g.
     * invalid connection, illegal table name and so on) method returns null
     */
    @Override
    public DescriptorCollection read() {
	DescriptorCollection result = null;

	if (conn != null) {
	    java.sql.Statement st = null;
	    ResultSet rs = null;
	    try {

		// check whether connection is open
		if (!conn.isClosed()) {
		    result = new DescriptorCollection();

		    st = conn.createStatement();
		    rs = st
			    .executeQuery("select id,name,description,parent_id,expression"
				    + " from " + tableName);

		    while (rs.next()) {
			int id = rs.getInt(1);
			String name = rs.getString(2);
			String description = rs.getString(3);
			int parent_id = rs.getInt(4);
			String expression = rs.getString(5);

			EventDescriptor descriptor = new EventDescriptor(id,
				parent_id);
			descriptor.setName(name);
			descriptor.setDescription(description);
			descriptor.setExpression(expression);

			result.add(descriptor);
		    }

		}

	    } catch (Exception e) {
		result = null;
	    } finally {
		cleanup(st, rs);
	    }
	}

	return result;
    }

    private void cleanup(java.sql.Statement st, ResultSet rs) {
	if (rs != null)
	    try {
		rs.close();
	    } catch (SQLException e) {
		// nothing todo here
	    }
	if (st != null)
	    try {
		st.close();
	    } catch (SQLException e) {
		// nothing todo here
	    }
    }

    Connection conn;
    String tableName;

}
